package dao.mysql;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import java.sql.*;

import model.LoginCredentials;
import model.Staff;


import dao.StaffDAO;
public class StaffDAOImpl implements StaffDAO {
DataSource dataSource=null;
Connection con=null;
PreparedStatement pstmt=null;

public StaffDAOImpl(){
	try{
		//dataSource=(DataSource) new InitialContext().lookup("java:comp/env/jdbc/fbs");
		//con=dataSource.getConnection();
		con=ConnectionProperties.getConnection();
	}
	catch(Exception ee){
		System.out.println(ee.getMessage());
	}
}
public ArrayList<Staff> combine(LoginCredentials log) throws SQLException{
	
	ArrayList<Staff> sArrayList=new ArrayList<Staff>();
	try {
	pstmt=con.prepareStatement("select * from Staff");
	ResultSet rs=pstmt.executeQuery();
	LoginCredentials log1=null;
	while(rs.next()){
		Staff s=new Staff();
		s.setStaffID(rs.getString("StaffID"));
		s.setName(rs.getString("Name"));
		s.setEmail(rs.getString("Email"));
		s.setPhoneNumber(rs.getString("PhoneNumber"));
		s.setReportTo(rs.getString("ReportTo"));
		log1=new LoginCredentialsDAOImpl().findLoginCredentials(rs.getString("StaffID"));
		s.setLoginCreds(log1);
		sArrayList.add(s);
	
	}
	}
	catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	finally
	{
		pstmt.close();
		con.close();
	}
	return sArrayList;
}
	@Override
	public void insertStaff(Staff staff) throws SQLException{
		// TODO Auto-generated method stub
		try{
			pstmt=con.prepareStatement("insert into Staff values(?,?,?,?,?)");
			pstmt.setString(1, staff.getStaffID());
			pstmt.setString(2, staff.getName());
			pstmt.setString(3, staff.getEmail());
			pstmt.setString(4,staff.getPhoneNumber() );
			pstmt.setString(5, staff.getReportTo());
			pstmt.executeUpdate();
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
			con.close();
		}
	}
	public ArrayList<Staff> SearchStaff(String staffName)throws SQLException{
		ArrayList<Staff> sArrayList=new ArrayList<Staff>();
		Staff s=new Staff();
		try{
			pstmt=con.prepareStatement("Select * From Staff s,LoginCredentials l where s.StaffID=l.StaffID and s.Name like ?");
			pstmt.setString(1, staffName);
			ResultSet rs=pstmt.executeQuery();
			LoginCredentials log1=null;
			
			while (rs.next()){
				
				s.setStaffID(rs.getString("StaffID"));
				s.setName(rs.getString("Name"));
				s.setEmail(rs.getString("Email"));
				s.setPhoneNumber(rs.getString("PhoneNumber"));
				s.setReportTo(rs.getString("ReportTo"));
				log1=new LoginCredentialsDAOImpl().findLoginCredentials(rs.getString("StaffID"));
				s.setLoginCreds(log1);
				sArrayList.add(s);
			}
			
			
		}catch (SQLException e){
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
			con.close();
		}
		return sArrayList;
	}

	@Override
	public void updateStaff(Staff staff) throws SQLException{
		// TODO Auto-generated method stub
		try {
			pstmt=con.prepareStatement("update Staff set Name=?,Email=?,PhoneNumber=?,ReportTo=? where StaffID=?");
			pstmt.setString(1, staff.getName());
			pstmt.setString(2, staff.getEmail());
			pstmt.setString(3, staff.getPhoneNumber());
			pstmt.setString(4, staff.getReportTo());
			pstmt.setString(5, staff.getStaffID());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
			con.close();
		}
	}

	@Override
	public void deleteStaff(String StaffID) throws SQLException{
		// TODO Auto-generated method stub
		try {
			pstmt=con.prepareStatement("delete from Staff where StaffID=?");
			pstmt.setString(1, StaffID);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
			con.close();
		}
		
	}

	@Override
	public Staff findStaff(String StaffID) throws SQLException{
		// TODO Auto-generated method stub
		Staff s=null;
		try {
			pstmt=con.prepareStatement("select * from Staff where StaffID=?");
			pstmt.setString(1, StaffID);
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()){
				s=new Staff();
				s.setStaffID(rs.getString("StaffID"));
				s.setName(rs.getString("Name"));
				s.setEmail(rs.getString("Email"));
				s.setPhoneNumber(rs.getString("PhoneNumber"));
				s.setReportTo(rs.getString("ReportTo"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
			con.close();
		}
		
		
		return s;
	}

	@Override
	public ArrayList<Staff> findAllStaff() throws SQLException{
		// TODO Auto-generated method stub
		ArrayList<Staff> sArrayList=new ArrayList<Staff>();
		try {
		pstmt=con.prepareStatement("select * from Staff");
		ResultSet rs=pstmt.executeQuery();
		while(rs.next()){
			Staff s=new Staff();
			s.setStaffID(rs.getString("StaffID"));
			s.setName(rs.getString("Name"));
			s.setEmail(rs.getString("Email"));
			s.setPhoneNumber(rs.getString("PhoneNumber"));
			s.setReportTo(rs.getString("ReportTo"));
			sArrayList.add(s);
		}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
			con.close();
		}
		return sArrayList;
	}
	@Override
	public String searchEmail(String staffID) throws SQLException {
		
		String email=null;
		try {
			pstmt=con.prepareStatement("select Email from staff s, logincredentials l where s.StaffID=? and s.StaffID=l.StaffID and l.Role='Manager';");
			pstmt.setString(1, staffID);
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()){
				
				email=(rs.getString("Email"));
				
			}
			
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				pstmt.close();
				con.close();
				
			}
		
		// TODO Auto-generated method stub
		return email;
	}

}
